﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Models;
namespace DAL
{
    public class IdentityDAL
    {
#pragma warning disable CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”
        public static string conn = ConfigurationSettings.AppSettings["con"].ToString();
#pragma warning restore CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”
        public static List<BanDing> getBindingSele(string rfkh)
        {
            List<BanDing> list = new List<BanDing>();
            string sql = "select ryid,ryxm from dbo.ryxx where rfkh =@rfkh";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@rfkh", rfkh)
            };
            SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql, param);
            if (sqlDataReader.Read())
            {
                list.Add(new BanDing
                {
                    ryid = Convert.ToInt32(sqlDataReader["ryid"]),
                    name = sqlDataReader["ryxm"].ToString()
                });
            }

            return list;
        }


        /// <summary>
        /// 插入idbind表记录
        /// </summary>
        /// <param name="CardId"></param>
        /// <param name="UserName"></param>
        /// <param name="openid"></param>
        /// <returns></returns>
        public static int insertIdbind(string CardId, string UserName, string pwd, string openid, string openid_wechat)
        {

            SqlParameter[] param = new SqlParameter[]
            {

                new SqlParameter("@CardId", CardId),
                new SqlParameter("@UserName", UserName),
                new SqlParameter("@pwd",pwd),
                new SqlParameter("@openid_wechat", openid_wechat),
                new SqlParameter("@openid", openid)
            };
            string sql = @" 
                               if not exists(select * from IdBind where rfkh=@CardId and openidwechat=@openid_wechat)    
                                begin
                                declare @ryid int      
                                    set @ryid= (select ryid from ryxx where  (Tel = @CardId or dh = @CardId or rybh = @CardId or sfz = @CardId or rfkh = @CardId)  and (ryxm=@UserName and password = @pwd))
                                    insert into IdBind(ryid,name, rfkh, openid,openidwechat,bindtime)values(@ryid,@UserName,@CardId,@openid,@openid_wechat,getdate())
                                 end   
                            ";

            return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, param);
        }


        /// <summary>
        /// 插入idbind表记录
        /// </summary>
        /// <param name="CardId"></param>
        /// <param name="UserName"></param>
        /// <param name="openid"></param>
        /// <returns></returns>
        public static int insertIdbind_Tel(string CardId, string UserName,string CUST_ID, string openid, string openid_wechat)
        {

            SqlParameter[] param = new SqlParameter[]
            {

                new SqlParameter("@CardId", CardId),
                new SqlParameter("@UserName", UserName),
                new SqlParameter("@CUST_ID", CUST_ID),
                new SqlParameter("@openid_wechat", openid_wechat),
                new SqlParameter("@openid", openid)
            };
            string sql = @" 
                               if not exists(select * from IdBind where rfkh=@CardId)    
                                begin
                                declare @ryid int      
                                    set @ryid= (select ryid from ryxx where  (Tel = @CardId or dh = @CardId or rybh = @CardId  or rfkh = @CardId)  and (bankcard=@CUST_ID))
                                    insert into IdBind(ryid,name, rfkh, openid,openidwechat,bindtime)values(@ryid,@UserName,@CardId,@openid,@openid_wechat,getdate())
                                 end   
                            ";

            return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, param);
        }

        public static string getOpenId(string rfkh)
        {
            string sql = "select openid from IdBind where rfkh=@rfkh";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@rfkh", rfkh)
            };
            SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql, param);
            string result = "";
            if (sqlDataReader.Read())
            {
                result = sqlDataReader["openid"].ToString();
            }
            
            return result;
        }

        public static string getOpenId_Tel(string PHONE_NO)
        {
            string sql = "select openid from IdBind where rfkh=@PHONE_NO";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@PHONE_NO", PHONE_NO)
            };
            SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql, param);
            string result = "";
            if (sqlDataReader.Read())
            {
                result = sqlDataReader["openid"].ToString();
            }

            return result;
        }

        //获取ryxx表总数+1
        public static int getCount()
        {
            try
            {
                string sql = "select top(1) ryid from ryxx order by ryid desc ";
                int num = Convert.ToInt32(SqlHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql));
                return num;
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错getCount：" + ex.Message);
                return -1;
            }

        }

        //通过姓名获取卡号
        public static string getRfkh(string ryxm)
        {
            string sql = "select rfkh from ryxx where rfkh =@ryxm";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ryxm", ryxm)
            };
            SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql, param);
            string result = "";
            if (sqlDataReader.Read())
            {
                result = sqlDataReader["rfkh"].ToString();
            }
         
            return result;
        }

        public static string getIsFeat( )
        {
            string sql = "select isfeat from systemset ";
            SqlParameter[] param = new SqlParameter[]
            {
                 
            };
            SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql, param);
            string result = "";
            if (sqlDataReader.Read())
            {
                result = sqlDataReader["isfeat"].ToString();
            }

            return result;
        }


        /// <summary>
        /// 获取消费限次，消费天限额,当天流水金额
        /// </summary>
        /// <param name="rfkh"></param>
        /// <returns></returns>
        public static DataTable Consumption_restriction(string rfkh, string type)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlParameter[] sp = new SqlParameter[]
                {
                    new SqlParameter("@rfkh",rfkh)
                };
                string sql = string.Empty;
                if (type == "ryxx")
                {
                    sql = "SELECT RYID,RFKH,RYBH,RFYE,GRYE,BTYE,RYXM,BMMC,RFZT,KLB,KHRQ,XC1,XC2,XC3,XC4,XFXE,YXQ,KXFJH,ZWBH,SFZ,TEL FROM RYXX WHERE bankcard = @rfkh";
                }       
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错方法Consumption_restriction：" + ex.Message);
                return dataTable;
            }
        }

        /// <summary>
        /// 通过编号查询余额
        /// </summary>
        /// <param name="user_phone"></param>
        /// <returns></returns>
        public static DataTable Select_CUST_ID(string rfkh)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@rfkh",rfkh)
            };
                string sql = "select top(1) bankcard,dz,bz from ryxx where 1=1 and rfzt='32' and bankcard=@rfkh";
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错方法Select_CUST_ID：" + ex.Message);
                return dataTable;
            }
        }

        //将需同步的账户充值id保存到人员数据里面
        public static string Up_ACC_DATA(string RFKH, double ACC_NO_01, double ACC_NO_02)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@RFKH",RFKH),
                new SqlParameter("@ACC_NO_01",ACC_NO_01),
                new SqlParameter("@ACC_NO_02",ACC_NO_02)
            };
            try
            {
                string sql = "update ryxx set rfye=@ACC_NO_01+@ACC_NO_02,grye=@ACC_NO_01,btye=@ACC_NO_02 where bankcard=@RFKH";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp).ToString();
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错Up_ACC_DATA：" + ex.Message);
                return null;
            }
        }

    }
}
